﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Shutterstock.Salesforce.SSISDownload.Logging;
using System.Diagnostics;
using Shutterstock.Salesforce.Tools.SFPartner;
using Shutterstock.Salesforce.Tools;
using System.IO;
using LumenWorks.Framework.IO.Csv;

namespace Shutterstock.Salesforce.SSISDownload.SFTableOperations
{
    /// <summary>
    /// Represents a Table replicator
    /// </summary>
    public static class TableReplicator
    {
        /// <summary>
        /// Replicate a salesforce table
        /// </summary>
        /// <param name=""></param>
        /// <param name="rp"></param>
        public static void Replicate(ReplicationParameter rp)
        {
            using (SqlConnection Dest = new SqlConnection(rp.DestConnectionString))
            {

                Dest.Open();

                //Build Table Query (Staging Table)
                SFTableInfo tableInfo = rp.SalesForceConnection.SalesForceService.GetTableInfo(rp.SFTableInfo.TableName);
                string CreateTable = tableInfo.GenerateCreateStagingTable();
                SqlCommand cmd = new SqlCommand(CreateTable, Dest);
                cmd.ExecuteNonQuery();
                

                //Fill Table
                string sql = tableInfo.GenerateSelectAll();
                string stagingName = tableInfo.GetStagingTableName();
                DateTime start = DateTime.Now;
                Debug.WriteLine(DateTime.Now.ToString());

                string CountSQL = String.Format("select rowcnt from sys.sysindexes SI where indid<2 and id=OBJECT_ID('{0}')", tableInfo.TableName);
                cmd.CommandText = CountSQL;
                object o = cmd.ExecuteScalar();

                int affectedRows = int.MaxValue;

                if (o != null)
                {
                    affectedRows = int.Parse(o.ToString());
                }

                

                cmd.Dispose();
                int rows = 0;
                if (!rp.UseBulkAPI)
                {
                    rows = UseStandardAPI(rp, Dest, cmd, sql, start);
                }
                else
                {
                    if (affectedRows > rp.BulkRowCutoff)
                    {
                        rows = UseBulkAPI(rp, Dest, cmd, sql, start);
                    }
                    else
                    {
                        rows = UseStandardAPI(rp, Dest, cmd, sql, start);
                    }
                }


              

                //Copy Indexes
                if (rp.MaintainIndexes)
                {
                    start = DateTime.Now;
                    IndexesReplicator.Replicate(Dest, rp.SFTableInfo.TableName, stagingName);
                    Logger.Log(FunctionType.CopyIndex, rp.SFTableInfo.TableName, TableTaskType.Replicate, start, rows);
                }

                //Copy Permissions
                List<string> permissionSqlStatements = new List<string>();
                if (rp.MaintainPermissions)
                {
                    CopyPermissions(rp, cmd, stagingName, start, permissionSqlStatements);

                }

                //Drop/SP_Rename
                StringBuilder drop = new StringBuilder();
                drop.AppendLine(String.Format(" IF object_id('{0}') is not null", rp.SFTableInfo.TableName));
                drop.AppendLine(" begin");
                drop.AppendLine(String.Format(" Drop Table [{0}] ", rp.SFTableInfo.TableName));
                drop.AppendLine(" end");

                string renameStatement = String.Format("sp_rename '{0}','{1}'", stagingName, rp.SFTableInfo.TableName);
                cmd.CommandText = drop.ToString();
                cmd.ExecuteNonQuery();

                cmd.CommandText = renameStatement;
                cmd.ExecuteNonQuery();

                Dest.Close();
                GC.Collect();
            }
            
        }


        private static void CopyPermissions(ReplicationParameter rp, SqlCommand cmd, string stagingName, DateTime start, List<string> permissionSqlStatements)
        {
            start = DateTime.Now;

            StringBuilder sb = new StringBuilder();
            sb.Append(" select 'grant ' + privilege_type + ' on ' + '{0}' + ' to ' + grantee");
            sb.Append(" from Information_schema.table_privileges where table_name='{1}' ");
            string permissionSql = String.Format(sb.ToString(), stagingName, rp.SFTableInfo.TableName);
            cmd.CommandText = permissionSql;
            SqlDataReader permissionReader = cmd.ExecuteReader();
            while (permissionReader.Read())
            {
                permissionSqlStatements.Add(permissionReader.GetString(0));
            }
            permissionReader.Close();
            permissionSqlStatements.ForEach(sqlStatement =>
            {
                cmd.CommandText = sqlStatement;
                cmd.ExecuteNonQuery();
            });
            Logger.Log(FunctionType.CopyPermission, rp.SFTableInfo.TableName, TableTaskType.Replicate, start, 0);
        }


        private static int UseBulkAPI(ReplicationParameter rp, SqlConnection Dest, SqlCommand cmd, string query, DateTime start)
        {
            var bqs =rp.SalesForceConnection;
            string path = String.Format("{0}/{1}Replicate.csv", rp.BulkPath, rp.SFTableInfo.TableName);
            if (File.Exists(path))
                File.Delete(path);
            bqs.ExecuteBlockingQuery(rp.SFTableInfo.TableName, ConcurrencyMode.Parallel, query, 30000, path);

            using (TextReader tr = new StreamReader(path))
            {
                string firstLine = tr.ReadLine();
                if (firstLine != "Records not found for this query")
                {
                    ((StreamReader)tr).BaseStream.Position = 0;
                    ((StreamReader)tr).DiscardBufferedData();

                    Logger.Log(FunctionType.BulkQuery, rp.SFTableInfo.TableName, TableTaskType.Update, start, -1);
                    using (CsvReader csv = new CsvReader(tr, true))
                    {
                        using (CSVDataReaderAdapter adapter = new CSVDataReaderAdapter(csv))
                        {
                            IDataReader reader = adapter;

                            SqlConnection conn = Dest;
                            if (conn.State != ConnectionState.Open)
                            {
                                conn.Open();
                            }
                            string tablename = rp.SFTableInfo.GetStagingTableName();

                            Dictionary<string, string> lookup = TableUtilities.GetDBCasedNames(tablename, conn);

                            List<string> decimals = TableUtilities.GetDBDecimals(tablename, Dest);
                            foreach (string decimalColumn in decimals)
                            {
                                adapter.AddDecimalOveride(decimalColumn);
                            }

                            using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
                            {

                                for (int i = 0; i < reader.FieldCount; i++)
                                {
                                    string s1 = reader.GetName(i);
                                    bulkCopy.ColumnMappings.Add(s1, lookup[s1.ToUpper()]);
                                }

                                bulkCopy.DestinationTableName = string.Format("[{0}]", tablename);
                                bulkCopy.BatchSize = rp.BatchSize;
                                bulkCopy.BulkCopyTimeout = 0; ;
                                bulkCopy.WriteToServer(reader);
                            }
                        }
                    }
                }
            }
            try
            {
                File.Delete(path);
            }
            catch
            {
            }
            return -1;
        }


        private static int UseStandardAPI(ReplicationParameter rp, SqlConnection Dest, SqlCommand cmd, string query, DateTime start)
        {
            int rows;
            DataTable dataTable;
            dataTable = rp.SalesForceConnection.SalesForceService.GetDataTable(query, rp.SFTableInfo);
            
            
            Logger.Log(FunctionType.WebServiceGetTable, rp.SFTableInfo.TableName, TableTaskType.Replicate, start, dataTable.Rows.Count);
            start = DateTime.Now;
            using (SqlBulkCopy BulkDestination = new SqlBulkCopy(Dest))
            {
                BulkDestination.DestinationTableName = '[' + rp.SFTableInfo.GetStagingTableName() + ']';
                BulkDestination.BulkCopyTimeout = 0;
                BulkDestination.BatchSize = rp.BatchSize;
                BulkDestination.WriteToServer(dataTable);
            }
            Logger.Log(FunctionType.BulkLoad, rp.SFTableInfo.TableName, TableTaskType.Replicate, start, dataTable.Rows.Count);


            rows = dataTable.Rows.Count;
            return rows;
        }
    }
}
